package org.tony.utils;


import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import org.tony.db.DbType;
import org.tony.entity.DbEntry;

/**
 * 数据库连接帮助类
 * @author tony
 *
 */
public class JdbcHelper {
	
	public static final DbEntry system = new DbEntry();
	public static final DbEntry backup = new DbEntry();
	
	public static final Map<String,String> driverClasses = new HashMap<String, String>();
	
	static{
		
		driverClasses.put(DbType.ORACLE.name(), "oracle.jdbc.driver.OracleDriver") ;
		driverClasses.put(DbType.MSSQL2005.name(), "com.microsoft.sqlserver.jdbc.SQLServerDriver") ;
		
		Properties pro = new Properties();
		try {
			pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("configs/system.properties")) ;
			String driver = pro.get("sys-conn-driver").toString() ;
			String dbname = pro.get("sys-conn-dbname").toString();
			String user = pro.get("sys-conn-user").toString();
			String pwd = pro.get("sys-conn-pass").toString() ;
			String sys_port = pro.get("sys-conn-port").toString() ;
			
			system.setDatabaseName(dbname) ;
			system.setDatabaseType("ORACLE") ;
			system.setDataSourceIp("localhost");
			system.setDriverClass(driver);
			system.setUsername(user);
			system.setPassword(pwd);
			system.setPort(Integer.parseInt(sys_port));
			
			String bkpdriver = pro.get("backup-conn-driver").toString() ;
			String bkpdbname = pro.get("backup-conn-dbname").toString();
			String bkpuser = pro.get("backup-conn-user").toString();
			String bkppwd = pro.get("backup-conn-pass").toString() ;
			String bkPort = pro.get("backup-conn-port").toString() ;
			
			backup.setPort(Integer.parseInt(bkPort)) ;
			backup.setDatabaseName(bkpdbname) ;
			backup.setDatabaseType("ORACLE") ;
			backup.setDataSourceIp("localhost");
			backup.setDriverClass(bkpdriver);
			backup.setUsername(bkpuser);
			backup.setPassword(bkppwd);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getSystemDbConnection() {
		return getConnection(system);
	}
	
	public static Connection getBakUpDbConnection() {
		return getConnection(backup);
	}
	
	
	public static boolean testConnection(DbEntry entry) {
		boolean result = false ; 
		Connection con = getConnection(entry);
		result = con == null?false : true ;
		JdbcHelper.closeConnection(con);
		return result;
	}
	
	public static void closeConnection(Connection conn) {
		try {
			if(conn!=null&&!conn.isClosed()) {
				conn.close() ;
			}
		} catch (SQLException e) {
			System.out.println("连接关闭失败");
		}
	}
	
	public static void closeStatement(Statement st) {
		try {
			if(st!=null) {
				st.close() ;
			}
		} catch (SQLException e) {
			System.out.println("连接关闭失败");
		}
	}
	

	public static void closeResultSet(ResultSet rs) {
		try {
			if(rs!=null) {
				rs.close() ;
			}
		} catch (SQLException e) {
			System.out.println("连接关闭失败");
		}
	}
	
	/**
	 * 根据数据源实体 获取连接对象
	 * @param entry
	 * @return
	 */
	public static Connection getConnection(DbEntry entry) {
		try {
			Class.forName(entry.getDriverClass()) ;
			String conStr = getConnectionString(entry);
			Connection con = DriverManager.getConnection(conStr, entry.getUsername(), entry.getPassword()) ;
			return con;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 根据数据源实体 生成对应的连接串
	 * @param entry
	 * @return
	 */
	public static String getConnectionString(DbEntry entry) {
		StringBuilder sb = new StringBuilder();
		switch(DbType.valueOf(entry.getDatabaseType())) {
			case ORACLE:
				sb.append("jdbc:oracle:thin:@");
				sb.append(entry.getDataSourceIp()) ;
				sb.append(":"+entry.getPort()+":") ;
				sb.append(entry.getDatabaseName()) ;
				return sb.toString();
			case MSSQL2005:
				sb.append("jdbc:sqlserver://");
				sb.append(entry.getDataSourceIp()) ;
				sb.append(":"+entry.getPort()+"; DatabaseName=") ;
				sb.append(entry.getDatabaseName()) ;
				return sb.toString();
		}
		return null ;
	}
	
	public static String getDbEntryTrigger(DbEntry entry,String targetTabName,String[] targetColoumn) {
		
		return null;
	}
	
	
}
